<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="com.uc.training.smadmin.gds.dao.GoodsDao">

    <!-- 获取热门推荐 -->
    <select id="getHotRecommend" resultClass="long">
		 SELECT
		 MAX(gds_property.id) AS propertyId
		 FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
		 where goods.status=1 and goods.is_delete=0
		 GROUP BY goods.id
		 order by goods.sales desc
		 limit 0,8
	</select>

    <select id="getHotRecommendCount" resultClass="java.lang.Integer">
		 SELECT count(1)
		 FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
		join t_gds_goods_pic gds_pic on gds_property.id=gds_pic.property_id
	</select>

    <!-- 查询分页列表 -->
    <select id="getGoodsList" parameterClass="java.util.List"
            resultClass="com.uc.training.smadmin.gds.re.GoodsRE">
        SELECT
        goods.category_id as categoryId,
        goods.name,
        goods.sales,
        goods.id as goodsId,
        gds_property.id as propertyId,
        gds_property.sale_price as salePrice
        FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
        where gds_property.id
        IN
        <iterate open="(" close=")" conjunction=",">
            #propertyIds[]#
        </iterate>
        order by goods.sales desc
    </select>

    <!-- 查询分页列表总记录数 -->
    <select id="getGoodsListCount" resultClass="long"
            parameterClass="com.uc.training.smadmin.gds.vo.GoodsListVO">
		SELECT
	        MAX(gds_property.id) AS propertyId
        FROM
	    t_gds_property gds_property
        LEFT JOIN t_gds_goods goods ON goods.id = gds_property.goods_id
        WHERE
	    goods.category_id = #categoryId#
        GROUP BY
	    goods.id
	</select>

    <select id="getGoodsDetailByGoodsId" resultClass="com.uc.training.smadmin.gds.re.GoodsDetailRE"
            parameterClass="long">
		select
		 goods.name,
		 goods.id as goodsId,
		goods.sales,
		goods.code,
		goods.detail,
		goods.status,
		goods.is_delete as isDelete,
		gds_property.id as propertyId,
		gds_property.sale_price as salePrice,
		gds_property.stock,
		gds_property.property,
		gds_property.is_discount as isDiscount,
		gds_property.discount_price as discountPrice
		 FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
		 where gds_property.goods_id=#goodsId#
	</select>

    <select id="getPicUrlByPropertyId" resultClass="com.uc.training.smadmin.gds.re.PropertyUrlRE" parameterClass="long">
		select
		gds_pic.property_id as propertyId,
		gds_pic.pic_url as picUrl
		from t_gds_goods_pic gds_pic
		 where gds_pic.property_id=#propertyId#
	</select>

    <select id="getPicUrlByPropertyIds" resultClass="com.uc.training.smadmin.gds.re.PropertyUrlRE"
            parameterClass="java.util.List">
        select
        gds_pic.property_id as propertyId,
        gds_pic.pic_url as picUrl
        from t_gds_goods_pic gds_pic
        where gds_pic.property_id
        IN
        <iterate open="(" close=")" conjunction=",">
            #propertyIds[]#
        </iterate>
    </select>

    <select id="getGoodsDetailByPropertyId" resultClass="com.uc.training.smadmin.gds.re.GoodsDetailRE"
            parameterClass="long">
		select
		 goods.name,
		 goods.id as goodsId,
		goods.sales,
		goods.code,
		goods.detail,
		goods.status,
		goods.is_delete as isDelete,
		gds_property.id as propertyId,
		gds_property.sale_price as salePrice,
		gds_property.stock,
		gds_property.property,
		gds_property.is_discount as isDiscount,
		gds_property.discount_price as discountPrice
		 FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
		 where gds_property.id=#propertyId#
	</select>

    <!--减库存之前，检查是否下架、删除，在更新时检查是否有库存-->
    <select id="selectGoodsStatus" parameterClass="com.uc.training.smadmin.gds.vo.GoodsStokeVO"
            resultClass="com.uc.training.smadmin.gds.re.GoodsStokeRE">
		select
		goods.is_delete as isDelete,
		goods.status,
		gds_property.stock as stoke,
        gds_property.id as propertyId,
        goods.name as goodsName,
        gds_property.property as goodsProperty
		 FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
		 where gds_property.id=#propertyId#
	</select>

    <update id="updateAndDeductStoke" parameterClass="com.uc.training.smadmin.gds.vo.GoodsStokeVO">
		update t_gds_property set stock=stock-#stoke# where id=#propertyId#
	</update>

    <update id="updateSales" parameterClass="com.uc.training.smadmin.gds.vo.GoodsStokeVO">
		update t_gds_goods set sales=#stoke#+sales where id=#goodsId#
	</update>

    <select id="searchCountByGoodsName" resultClass="java.lang.Integer" parameterClass="com.uc.training.smadmin.gds.vo.GoodsListVO">
		SELECT
		count(1)
		FROM
		t_gds_goods goods
        where goods.is_delete=0 and goods.status=1 and goods.name like concat("%",#name#,"%")
	</select>

    <select id="searchByGoodsName" resultClass="long" parameterClass="com.uc.training.smadmin.gds.vo.GoodsListVO">
        SELECT
		max(gds_property.id) AS propertyId
		FROM
		t_gds_property gds_property
		left JOIN t_gds_goods goods ON goods.id = gds_property.goods_id
        where goods.is_delete=0 and goods.status=1 and goods.name like concat("%",#name#,"%")
        GROUP BY goods.id
        <isNotEmpty property="offset">
            limit #offset#, #pageSize#
        </isNotEmpty>
    </select>

    <select id="searchByPropertyId" resultClass="com.uc.training.smadmin.gds.re.GoodsRE"
            parameterClass="java.util.List">
        SELECT
        goods.name,
        goods.id as goodsId,
        goods.sales,
        gds_property.id as propertyId,
        gds_property.sale_price as salePrice
        FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
        where gds_property.id
        IN
        <iterate open="(" close=")" conjunction=",">
            #propertyIds[]#
        </iterate>
        order by goods.sales desc
    </select>

    <select id="selectHotTag" resultClass="com.uc.training.smadmin.gds.model.HotTag">
        SELECT
        tag.id,
        tag.tag,
        tag.sort_num as sortNum
        FROM t_gds_hot_tag tag
        order by tag.sort_num asc
        limit 0, 10
    </select>

    <!-- 获取商品数量 -->
    <select id="getAdminGoodsListCount" resultClass="java.lang.Long" parameterClass="com.uc.training.smadmin.gds.vo.GoodsListVO">
        SELECT
        count(1)
        FROM t_gds_goods gg
        <dynamic prepend="where">
            <isNotEmpty prepend="and">
                gg.is_delete = 0
            </isNotEmpty>
            <isNotEmpty property="code" prepend="and">
                gg.code LIKE concat("%",#code#,"%")
            </isNotEmpty>
            <isNotEmpty property="name" prepend="and">
                gg.name LIKE concat("%",#name#,"%")
            </isNotEmpty>
            <isNotEmpty property="categoryId" prepend="and">
                gg.category_id = #categoryId#
            </isNotEmpty>
        </dynamic>
    </select>

    <!-- 后台分页查询商品信息 -->
    <select id="getAdminGoodsList" parameterClass="com.uc.training.smadmin.gds.vo.GoodsListVO" resultClass="com.uc.training.smadmin.gds.re.AdminGoodsRE">
        SELECT
        gg.id AS id,
        gg.code AS code,
        gg.name AS  name,
        gg.category_id AS categoryId,
        gc.name AS categoryName,
        gg.sales AS  sales,
        gg.detail  AS detail,
        gg.status  AS status
        FROM
        t_gds_goods  gg LEFT JOIN t_gds_category   gc ON  gg.category_id = gc.id
        <dynamic prepend="where">
            <isNotEmpty prepend="and">
                gg.is_delete = 0
            </isNotEmpty>
            <isNotEmpty property="code" prepend="and">
                gg.code LIKE concat("%",#code#,"%")
            </isNotEmpty>
            <isNotEmpty property="name" prepend="and">
                gg.name LIKE concat("%",#name#,"%")
            </isNotEmpty>
            <isNotEmpty property="categoryId" prepend="and">
                gg.category_id = #categoryId#
            </isNotEmpty>
        </dynamic>
        ORDER BY gg.id ASC
        <isNotNull property="offset">
            limit #offset#, #pageSize#
        </isNotNull>
    </select>

    <!-- 根据id获取商品表信息 -->
    <select id="getGoodsById" parameterClass="Long" resultClass="com.uc.training.smadmin.gds.re.GoodsRE">
        SELECT
        id AS goodsId,
        `name`,
        category_id AS categoryId,
        detail,
        status
        FROM t_gds_goods
        where id = #id#
    </select>

    <insert id="insertGoods" parameterClass="com.uc.training.smadmin.gds.model.Goods">
        INSERT INTO t_gds_goods
        (code, name, category_id, sales, detail, status, is_delete, create_emp, modify_emp)
        VALUES (#code#, #name#, #categoryId#, 0, #detail#, 0, 0, #createEmp#, #modifyEmp#)
        <selectKey resultClass="long" keyProperty="id">
            SELECT LAST_INSERT_ID() as id;
        </selectKey>
    </insert>

    <!--更新商品属性-->
    <update id="updateGoods" parameterClass="com.uc.training.smadmin.gds.vo.AdminUpdateGoodsVO">
        UPDATE t_gds_goods
        SET
              name=#name#,
              category_id=#categoryId#,
              detail=#detail#,
              modify_emp=#modifyEmp#
        WHERE id = #id#
    </update>

    <!--商品上架-->
    <update id="pullOnGoods" parameterClass="com.uc.training.smadmin.gds.vo.AdminPullGoodsVO">
        UPDATE t_gds_goods
        SET
              status=1,
              modify_emp=#modifyEmp#
        WHERE id = #id#
    </update>
    <!--商品下架-->
    <update id="pullOffGoods" parameterClass="com.uc.training.smadmin.gds.vo.AdminPullGoodsVO">
        UPDATE t_gds_goods
        SET
              status=0,
              modify_emp=#modifyEmp#
        WHERE id = #id#
    </update>

    <!--逻辑删除-->
    <update id="logicDeleteGoods" parameterClass="Long">
        UPDATE t_gds_goods
        SET  is_delete=1
        WHERE id = #id#
    </update>

    <select id="getGoodsDetailByPropertyIds" resultClass="com.uc.training.smadmin.gds.re.GoodsDetailRE"
            parameterClass="java.util.List">
        select
        goods.name,
        goods.sales,
        goods.code,
        goods.detail,
        goods.status,
        gds_property.id as propertyId,
        gds_property.sale_price as salePrice,
        gds_property.stock,
        gds_property.property,
        gds_property.is_discount as isDiscount,
        gds_property.discount_price as discountPrice
        FROM t_gds_property gds_property join t_gds_goods goods on goods.id=gds_property.goods_id
        where gds_property.id
        IN
        <iterate open="(" close=")" conjunction=",">
            #propertyIds[]#
        </iterate>
    </select>
</sqlMap>